using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;

namespace OgilvyOne.SqlServerDAL
{
    public class SSiteRegister : ISiteRegister
    {
        // Methods
        public bool DeleteByIdentity(int id)
        {
            bool flag = false;
            int num = 0;
            string cmdText = "";
            cmdText = "DELETE FROM siteRegister WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10) };
            cmdParms[0].Value = id;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        if (num > 0)
                        {
                            flag = true;
                        }
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return flag;
                }
            }
        }

        public MSiteRegister GetRowByIdentity(int id)
        {
            MSiteRegister register = null;
            string cmdText = "";
            cmdText = "SELECT id,userName,telarea,tel,mobile,email,address,ouserName,otelarea,otel,omobile,bankName,offshootName,totalSum,moneyType,buyYear,buyMonth,buyDay,checkNo1,checkNo2,q1,q2,q3,q4,q5,isrdt FROM siteRegister WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10) };
            cmdParms[0].Value = id;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    try
                    {
                        register = new MSiteRegister();
                        register.Id = Convert.ToInt32(reader[0]);
                        register.UserName = Convert.ToString(reader[1]);
                        register.Telarea = Convert.ToString(reader[2]);
                        register.Tel = Convert.ToString(reader[3]);
                        register.Mobile = Convert.ToString(reader[4]);
                        register.Email = Convert.ToString(reader[5]);
                        register.Address = Convert.ToString(reader[6]);
                        register.OuserName = Convert.ToString(reader[7]);
                        register.Otelarea = Convert.ToString(reader[8]);
                        register.Otel = Convert.ToString(reader[9]);
                        register.Omobile = Convert.ToString(reader[10]);
                        register.BankName = Convert.ToString(reader[11]);
                        register.OffshootName = Convert.ToString(reader[12]);
                        register.TotalSum = Convert.ToString(reader[13]);
                        register.MoneyType = Convert.ToString(reader[14]);
                        register.BuyYear = Convert.ToString(reader[15]);
                        register.BuyMonth = Convert.ToString(reader[0x10]);
                        register.BuyDay = Convert.ToString(reader[0x11]);
                        register.CheckNo1 = Convert.ToString(reader[0x12]);
                        register.CheckNo2 = Convert.ToString(reader[0x13]);
                        register.Q1 = Convert.ToString(reader[20]);
                        register.Q2 = Convert.ToString(reader[0x15]);
                        register.Q3 = Convert.ToString(reader[0x16]);
                        register.Q4 = Convert.ToString(reader[0x17]);
                        register.Q5 = Convert.ToString(reader[0x18]);
                        register.Isrdt = Convert.ToString(reader[0x19]);
                    }
                    catch (SqlException exception)
                    {
                        Console.WriteLine(exception.Message);
                    }
                }
            }
            return register;
        }

        public DataSet GetRows(string condition)
        {
            DataSet dataSet = new DataSet();
            string str = "SELECT id,userName,telarea,tel,mobile,email,address,ouserName,otelarea,otel,omobile,bankName,offshootName,totalSum,moneyType,buyYear,buyMonth,buyDay,checkNo1,checkNo2,q1,q2,q3,q4,q5,isrdt FROM siteRegister " + condition;
            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                try
                {
                    adapter.SelectCommand = new SqlCommand();
                    adapter.SelectCommand.CommandText = str;
                    adapter.SelectCommand.Connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    adapter.Fill(dataSet);
                }
                catch (SqlException exception)
                {
                    Console.WriteLine(exception.Message);
                }
            }
            return dataSet;
        }

        public int Insert(MSiteRegister data)
        {
            int num = 0;
            string cmdText = "INSERT INTO siteRegister(userName,telarea,tel,mobile,email,address,ouserName,otelarea,otel,omobile,bankName,offshootName,totalSum,moneyType,buyYear,buyMonth,buyDay,checkNo1,checkNo2,q1,q2,q3,q4,q5) VALUES(@userName, @telarea, @tel, @mobile, @email, @address, @ouserName, @otelarea, @otel, @omobile, @bankName, @offshootName, @totalSum, @moneyType, @buyYear, @buyMonth, @buyDay, @checkNo1, @checkNo2, @q1, @q2, @q3, @q4, @q5)";
            SqlParameter[] cmdParms = new SqlParameter[] { 
            new SqlParameter("@userName", SqlDbType.NVarChar, 50), new SqlParameter("@telarea", SqlDbType.NVarChar, 50), new SqlParameter("@tel", SqlDbType.NVarChar, 50), new SqlParameter("@mobile", SqlDbType.NVarChar, 50), new SqlParameter("@email", SqlDbType.NVarChar, 200), new SqlParameter("@address", SqlDbType.NVarChar, 500), new SqlParameter("@ouserName", SqlDbType.NVarChar, 50), new SqlParameter("@otelarea", SqlDbType.NVarChar, 50), new SqlParameter("@otel", SqlDbType.NVarChar, 50), new SqlParameter("@omobile", SqlDbType.NVarChar, 50), new SqlParameter("@bankName", SqlDbType.NVarChar, 200), new SqlParameter("@offshootName", SqlDbType.NVarChar, 200), new SqlParameter("@totalSum", SqlDbType.NVarChar, 50), new SqlParameter("@moneyType", SqlDbType.NVarChar, 50), new SqlParameter("@buyYear", SqlDbType.NVarChar, 50), new SqlParameter("@buyMonth", SqlDbType.NVarChar, 50), 
            new SqlParameter("@buyDay", SqlDbType.NVarChar, 50), new SqlParameter("@checkNo1", SqlDbType.NVarChar, 50), new SqlParameter("@checkNo2", SqlDbType.NVarChar, 50), new SqlParameter("@q1", SqlDbType.NVarChar, 50), new SqlParameter("@q2", SqlDbType.NVarChar, 50), new SqlParameter("@q3", SqlDbType.NVarChar, 50), new SqlParameter("@q4", SqlDbType.NVarChar, 50), new SqlParameter("@q5", SqlDbType.NVarChar, 50)
         };
            cmdParms[0].Value = data.UserName;
            cmdParms[1].Value = data.Telarea;
            cmdParms[2].Value = data.Tel;
            cmdParms[3].Value = data.Mobile;
            cmdParms[4].Value = data.Email;
            cmdParms[5].Value = data.Address;
            cmdParms[6].Value = data.OuserName;
            cmdParms[7].Value = data.Otelarea;
            cmdParms[8].Value = data.Otel;
            cmdParms[9].Value = data.Omobile;
            cmdParms[10].Value = data.BankName;
            cmdParms[11].Value = data.OffshootName;
            cmdParms[12].Value = data.TotalSum;
            cmdParms[13].Value = data.MoneyType;
            cmdParms[14].Value = data.BuyYear;
            cmdParms[15].Value = data.BuyMonth;
            cmdParms[0x10].Value = data.BuyDay;
            cmdParms[0x11].Value = data.CheckNo1;
            cmdParms[0x12].Value = data.CheckNo2;
            cmdParms[0x13].Value = data.Q1;
            cmdParms[20].Value = data.Q2;
            cmdParms[0x15].Value = data.Q3;
            cmdParms[0x16].Value = data.Q4;
            cmdParms[0x17].Value = data.Q5;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return num;
                }
            }
        }

        public bool ModifyByIdentity(MSiteRegister data)
        {
            bool flag = false;
            int num = 0;
            string cmdText = "";
            cmdText = "UPDATE siteRegister SET userName=@userName,telarea=@telarea,tel=@tel,mobile=@mobile,email=@email,address=@address,ouserName=@ouserName,otelarea=@otelarea,otel=@otel,omobile=@omobile,bankName=@bankName,offshootName=@offshootName,totalSum=@totalSum,moneyType=@moneyType,buyYear=@buyYear,buyMonth=@buyMonth,buyDay=@buyDay,checkNo1=@checkNo1,checkNo2=@checkNo2,q1=@q1,q2=@q2,q3=@q3,q4=@q4,q5=@q5 WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { 
            new SqlParameter("@id", SqlDbType.Int, 10), new SqlParameter("@userName", SqlDbType.NVarChar, 50), new SqlParameter("@telarea", SqlDbType.NVarChar, 50), new SqlParameter("@tel", SqlDbType.NVarChar, 50), new SqlParameter("@mobile", SqlDbType.NVarChar, 50), new SqlParameter("@email", SqlDbType.NVarChar, 200), new SqlParameter("@address", SqlDbType.NVarChar, 500), new SqlParameter("@ouserName", SqlDbType.NVarChar, 50), new SqlParameter("@otelarea", SqlDbType.NVarChar, 50), new SqlParameter("@otel", SqlDbType.NVarChar, 50), new SqlParameter("@omobile", SqlDbType.NVarChar, 50), new SqlParameter("@bankName", SqlDbType.NVarChar, 200), new SqlParameter("@offshootName", SqlDbType.NVarChar, 200), new SqlParameter("@totalSum", SqlDbType.NVarChar, 50), new SqlParameter("@moneyType", SqlDbType.NVarChar, 50), new SqlParameter("@buyYear", SqlDbType.NVarChar, 50), 
            new SqlParameter("@buyMonth", SqlDbType.NVarChar, 50), new SqlParameter("@buyDay", SqlDbType.NVarChar, 50), new SqlParameter("@checkNo1", SqlDbType.NVarChar, 50), new SqlParameter("@checkNo2", SqlDbType.NVarChar, 50), new SqlParameter("@q1", SqlDbType.NVarChar, 50), new SqlParameter("@q2", SqlDbType.NVarChar, 50), new SqlParameter("@q3", SqlDbType.NVarChar, 50), new SqlParameter("@q4", SqlDbType.NVarChar, 50), new SqlParameter("@q5", SqlDbType.NVarChar, 50)
         };
            cmdParms[0].Value = data.Id;
            cmdParms[1].Value = data.UserName;
            cmdParms[2].Value = data.Telarea;
            cmdParms[3].Value = data.Tel;
            cmdParms[4].Value = data.Mobile;
            cmdParms[5].Value = data.Email;
            cmdParms[6].Value = data.Address;
            cmdParms[7].Value = data.OuserName;
            cmdParms[8].Value = data.Otelarea;
            cmdParms[9].Value = data.Otel;
            cmdParms[10].Value = data.Omobile;
            cmdParms[11].Value = data.BankName;
            cmdParms[12].Value = data.OffshootName;
            cmdParms[13].Value = data.TotalSum;
            cmdParms[14].Value = data.MoneyType;
            cmdParms[15].Value = data.BuyYear;
            cmdParms[0x10].Value = data.BuyMonth;
            cmdParms[0x11].Value = data.BuyDay;
            cmdParms[0x12].Value = data.CheckNo1;
            cmdParms[0x13].Value = data.CheckNo2;
            cmdParms[20].Value = data.Q1;
            cmdParms[0x15].Value = data.Q2;
            cmdParms[0x16].Value = data.Q3;
            cmdParms[0x17].Value = data.Q4;
            cmdParms[0x18].Value = data.Q5;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        if (num > 0)
                        {
                            flag = true;
                        }
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return flag;
                }
            }
        }
    }
}